FREQUENCY Excel Function

The FREQUENCY function in Excel is an inbuilt Statistical function that determines the number of times values appear within a specific value range. The result is a vertical array of numeric values. Users can use FREQUENCY in Excel to calculate the frequency distribution in financial modeling.

For example, the below image shows students’ score details and the cut-offs.

Suppose we must populate the count of students in each score range in column F. Then, we can find FREQUENCY in Excel to determine the required data.

As the FREQUENCY() returns an array of numbers, the FREQUENCY in Excel equation gets executed as an array excel formula, as depicted in the above image.

In the above illustration, the FREQUENCY in Excel function counts and displays the count of scores falling in each score range. Thus, it determines the count of students in each cut-off interval.

The formula for FREQUENCY in Excel is:

where,

The two arguments in the FREQUENCY in Excel equation are mandatory.

On the other hand, the function for FREQUENCY in Excel will return:

When using the function to find FREQUENCY in Excel:

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

The steps to use the function for FREQUENCY in Excel are:

Here is a FREQUENCY in Excel example to understand the above steps.

Suppose our source table contains a list of applicants and their ages. The bin details are also available, and we need to determine the count of applicants in each age interval in column F.

The steps to use the function for FREQUENCY in Excel to determine the required data are:

Then, enter the below FREQUENCY().=FREQUENCY(B2:B11,D2:D7)

It thus generates the array values containing the frequencies or count of applicants in each age interval in cells F2:F7.

The equation converts into an array formula once we insert the above FREQUENCY in Excel example, using the Ctrl + Shift + Enter keys.

The values in column D (Bins) show the intervals to group the age values of column B. For instance, the ages 23, 22, and 25, fall in the second interval, which is the second bin value in column D. Thus, three applicants belong to this particular age bracket, and we get the value 3 in cell F3.

Here are a few more examples to explain the FREQUENCY in Excel meaning.

Suppose we have source data as depicted in the image below.

And we need to determine the number of rooms having three occupants. We can apply the FREQUENCY in Excel formula in cell E2 to perform the required calculations.

Step 1: To begin with, select cell E2 and choose the option Formulas → More Functions → Statistical → FREQUENCY.

Step 2: The Function Arguments window opens up.

While cell E2 gets populated with the FREQUENCY(), we can understand the FREQUENCY in Excel meaning in the Function Arguments window. When we enter the excel cell references in the respective arguments, it displays the values, calculations, and the final result.

Step 3: Next, click OK in the Function Arguments window to view the result in cell E2.

The FREQUENCY() returns the total number of occurrences of the value 3 in the cell range B2:B16.

Please Note: Since the above example has only one bin value, and we need to show the FREQUENCY() output in one cell, the function gets executed as a regular formula. Therefore, we do not need to execute it as an array formula.

Further, if the bin value were 4, the function for FREQUENCY in Excel would return a value of 15, as the corresponding bin value interval would be 0-4. And all values in cells B2:B16 fall in the range 0-4.

Let us see how to use FREQUENCY with other functions SUM and IF excel function.

Assume we have an items list as depicted in the image below. Now, we need to find the total number of unique items in the list to display the result in cell B19.

Each item in column B has a unique item code in column A. So we can use the IF() and SUM Excel Function with FREQUENCY in Excel and count the unique item codes. It will give us the required result.

Step 1: First, choose cell B19, type the below formula, and then, press Enter to count the unique item codes.

=SUM(IF(FREQUENCY(A2:A16,A2:A16)>0,1,0))

Thus, there are ten unique items in column B.

Here is an example of using FREQUENCY in Excel to determine the cumulative frequency percentage.

Consider the table below. It shows the laps and the time taken to complete each lap.

Suppose the bin values are available. We need to determine the laps frequencies and we require to calculate the cumulative frequency percentage for our analysis. We can perform these calculations using FREQUENCY in Excel function.

Step 1: To begin with, choose cells F2:F6 and type the below FREQUENCY().

=FREQUENCY(B2:B8,D2:D6)

Step 2: Next, press the Ctrl + Shift + Enter keys to execute the FREQUENCY() as an array formula.

Step 3: Now, to show the frequencies in column F as percentages in column G, choose cell G2, type the below formula, and press Enter.

=F2/SUM($F$2:$F$6)

Step 4: Next, drag the fill handle downwards to copy the formula in cells G3:G6.

Step 5: Now, select cells G2:G6 and set the Number Format in the Home tab as Percentage.

Step 6: Next, choose cell H2, type the below formula using SUM, and press Enter to determine the cumulative frequency percentage.

=SUM(F$2:F2)/SUM(F$2:F$6)

And as explained previously, set the Number Format for the cell H2 as Percentage.

Step 7: Drag the fill handle downwards to copy the formula in cells H3:H6.

Thus, with the above data, we can perform a time analysis for the laps and even create histograms and column charts for an in-depth analysis.

We can find FREQUENCY in Excel using the FREQUENCY formula. It returns how many times values appear in a given range as a vertical array of numeric values.

If we have to count the frequency of a value in only one bin or interval, we would typically require one cell to display the function output.

In such cases, we can select the target cell and go to Formulas → More Functions → Statistical → FREQUENCY to open the Function Arguments window.

Here we can enter the argument values and click OK to get the result in the target cell.

We can refer to Example 1 in the above article to understand the steps in detail.

If the output is an array, we can use the above method. But once the FREQUENCY formula gets executed in one cell, we must select the required target array range. And then click on the formula in the Formula Bar and press the Ctrl + Shift + Enter keys together to execute the FREQUENCY() as an array formula.

Otherwise, we can select the required target array range where we want to display the FREQUENCY() output and type the FREQUENCY formula. Then press the Ctrl + Shift + Enter keys together to execute the FREQUENCY() as an array formula.

We can do a FREQUENCY histogram in Excel using the below steps. Let us see them with an example.Suppose we require to determine the height frequencies for the below table and then create a FREQUENCY histogram in Excel.

Step 1: First, select cells E2:E6 and type the below FREQUENCY().=FREQUENCY(A2:A11,C2:C6)

Step 2: Next, select cell range D1:E6 and then, choose the option Insert → Insert Column or Bar Chart → Clustered 2-D Column chart.

Step 4: Next, right-click on a bar in the graph to choose the Format Data Series option from the context menu.Now, set a lower value for Gap Width, say 5%, to make the graph appear like a histogram.

Step 5: Then, click on the ‘+’ icon, Chart Elements and then enable the Axis Titles option and update the axis titles and chart title.Once updated, the final FREQUENCY histogram in Excel will be:

We have to use Ctrl + Shift + Enter for the FREQUENCY function since it returns a vertical array of numbers as the frequencies.So, to display the list of frequencies, first select the target array range and enter the FREQUENCY(). We will then have to press the Ctrl + Shift + Enter keys together to execute it as an array formula.

This article must be helpful to understand FREQUENCY in Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to FREQUENCY Function in Excel. Here we discuss how to use the FREQUENCY formula with examples and downloadable excel template. You can learn more from the following articles –